﻿CREATE FUNCTION [dbo].[fn_CFont](@cExpressionSearched [nvarchar](4000), @TransformType [int] = 21)
RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER
AS 
begin
	declare @lenExpressionSearched smallint, @lenSearchExpression smallint, @lenReplacementExpression smallint,
	@i  smallint,  @j  smallint,  @flag bit, @cExpressionTranslated  nvarchar(4000),
	@cSearchExpression nvarchar(36),  @cReplacementExpression nvarchar(36),
	@chrUnicode nvarchar(36), @chrStandard nvarchar(36),@chrEnglish nvarchar(36),@chrEnglish1 nvarchar(36),@chrDOSKGD nvarchar(36)
SET @cExpressionSearched = @cExpressionSearched COLLATE SQL_Latin1_General_CP1251_CS_AS
	SET @chrUnicode=N'აბგდევზთიკლმნოპჟრსტუფქღყშჩცძწჭხჯჰ№' -- 1 - Unicode Geo 
	SET @chrStandard=N'ÀÁÂÃÄÅÆÈÉÊËÌÍÏÐÑÒÓÔÖ×ØÙÚÛÜÝÞßàáãä¹' -- 2 - Standard Geo
	SET @chrEnglish=N'abgdevzTiklmnopJrstufqRySCcZwWxjh#' -- 3 - English Geo
	SET @chrEnglish1=N'f<ultdpsbrkvyjg;hcneamq>ixwoz[]/'   -- 4 - English1 Geo For DOS Goodwill
	SET @chrDOSKGD=N'°±²µ·¸½¾ÆÐÒÓÔÕÖÛÜÝÞßòóôõö÷øùúûüýþ'  -- 5 - Geo KGD DOS


IF @TransformType = 12 -- GEO: Unicode to Standard
SELECT @cSearchExpression = @chrUnicode, @cReplacementExpression = @chrStandard

IF @TransformType = 13 -- GEO: Unicode to English
SELECT @cSearchExpression = @chrUnicode, @cReplacementExpression = @chrEnglish

IF @TransformType = 21 -- GEO:  Standard to Unicode
SELECT @cSearchExpression = @chrStandard, @cReplacementExpression = @chrUnicode

IF @TransformType = 23 -- GEO:  Standard to English
SELECT @cSearchExpression = @chrStandard, @cReplacementExpression = @chrEnglish

IF @TransformType = 31 -- GEO:  English to Unicode
SELECT @cSearchExpression = @chrEnglish, @cReplacementExpression = @chrUnicode

IF @TransformType = 32 -- GEO:  English to Standard
SELECT @cSearchExpression = @chrEnglish, @cReplacementExpression = @chrStandard

IF @TransformType = 41 -- GEO:  English1 Geo For DOS Goodwill to Unicode
SELECT @cSearchExpression = @chrEnglish1, @cReplacementExpression = @chrUnicode

IF @TransformType = 42 -- GEO:  English1 Geo For DOS Goodwill to Standard
SELECT @cSearchExpression = @chrEnglish1, @cReplacementExpression = @chrStandard

IF @TransformType = 14 -- GEO: Unicode to English1 Geo For DOS Goodwill
SELECT @cSearchExpression = @chrUnicode, @cReplacementExpression = @chrEnglish1

IF @TransformType = 24 -- GEO:  Standard to English1 Geo For DOS Goodwill
SELECT @cSearchExpression = @chrStandard, @cReplacementExpression = @chrEnglish1

IF @TransformType = 51 -- GEO:  Geo KGD DOS to Unicode
SELECT @cSearchExpression = @chrDOSKGD, @cReplacementExpression = @chrUnicode

IF @TransformType = 52 -- GEO:  Geo KGD DOSl to Standard
SELECT @cSearchExpression = @chrDOSKGD, @cReplacementExpression = @chrStandard

IF @TransformType = 15 -- GEO: Unicode to Geo KGD DOS
SELECT @cSearchExpression = @chrUnicode, @cReplacementExpression = @chrDOSKGD

IF @TransformType = 25 -- GEO:  Standard to Geo KGD DOS
SELECT @cSearchExpression = @chrStandard, @cReplacementExpression = @chrDOSKGD


      select  @cExpressionTranslated = N'',  @i = 1, @flag = 0, 
              @lenExpressionSearched =  datalength(@cExpressionSearched)/(case SQL_VARIANT_PROPERTY(@cExpressionSearched,'BaseType') when 'nvarchar' then 2  else 1 end),
              @lenSearchExpression =  datalength(@cSearchExpression)/(case SQL_VARIANT_PROPERTY(@cSearchExpression,'BaseType') when 'nvarchar' then 2  else 1 end),
              @lenReplacementExpression =  datalength(@cReplacementExpression)/(case SQL_VARIANT_PROPERTY(@cReplacementExpression,'BaseType') when 'nvarchar' then 2  else 1 end)  -- for unicode
    
     if @lenReplacementExpression > @lenSearchExpression
         select @cReplacementExpression = left(@cReplacementExpression, @lenSearchExpression),   @lenReplacementExpression = @lenSearchExpression

     while @i  <=   @lenReplacementExpression
--       if  charindex(substring(@cReplacementExpression, @i, 1) COLLATE Latin1_General_BIN, @cSearchExpression COLLATE Latin1_General_BIN, @i + 1) > 0
       if  charindex(substring(@cReplacementExpression, @i, 1)  , @cSearchExpression  COLLATE SQL_1xCompat_CP850_CI_AS, @i + 1) > 0
         begin
            select  @flag = 1
            break
         end
       else
         select @i = @i + 1

   if @lenExpressionSearched = 4000  -- built-in function replace do not works correctly if the length of the string is 4000     MS SQL Server 2000, SP4
      if  charindex(right(@cExpressionSearched,1) COLLATE Latin1_General_BIN, @cSearchExpression COLLATE Latin1_General_BIN) > 0     -- I did run this example and validated the erroneous result
           select  @flag = 1                                                              -- select right(replace(replicate(N'z',3999)+N'i', N'i', N'B'),1) -- Displays i  but this is incorrect result, correct result is B

   select @i = 1

    if @flag  =  0
       -- this algorithm does not work always as
       -- select dbo.CHRTRAN('eaba','ba','a') -- Displays  e  Error !!!  ea  Correctly
       begin
          while @i  <=   @lenSearchExpression
            select  @cExpressionSearched = replace(@cExpressionSearched  COLLATE Latin1_General_BIN, 
                                                   substring(@cSearchExpression, @i, 1)   COLLATE Latin1_General_BIN,
                                                   substring(@cReplacementExpression, @i, 1)   COLLATE Latin1_General_BIN ),
                    @i =  @i + 1 
          select  @cExpressionTranslated = @cExpressionSearched
       end
    else
       while @i  <=   @lenExpressionSearched
          begin
             select @j  =  charindex(substring(@cExpressionSearched, @i, 1) COLLATE Latin1_General_BIN, @cSearchExpression COLLATE Latin1_General_BIN)
               if  @j  > 0
                   select  @cExpressionTranslated = @cExpressionTranslated + substring(@cReplacementExpression, @j , 1)
               else
                  select  @cExpressionTranslated = @cExpressionTranslated + substring(@cExpressionSearched, @i, 1)
             select   @i =  @i + 1 
          end
   return  @cExpressionTranslated
  end


